package dao;

import vo.Course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

public class CourseDao {
    private Connection conn = null;

    public boolean deleteCourse(String id) throws Exception {
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "delete from course where id='" + id + "'";
        int i = stat.executeUpdate(sql);
        closeConnection();
        return i == 1;
    }

    //数据库连接
    private void initConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentManagerDB?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true", "StuManager", "123456");
    }

    private void closeConnection() throws Exception {
        conn.close();
    }

    public ArrayList<Course> findAll() throws Exception {
        ArrayList<Course> al = new ArrayList<>();
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from course";
        ResultSet rs = stat.executeQuery(sql);
        getAllCourse(al, rs);
        closeConnection();
        return al;
    }

    public void updateCourseInfo(String name, String teacher, String college, String stu_id,String id) throws Exception {

        initConnection();
        String sql = "update course set name=?, teacher=?, college=?,stu_id=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        ps.setString(2, teacher);
        ps.setString(3, college);
        ps.setString(4, stu_id);
        ps.setInt(5, Integer.valueOf(id));
        ps.executeUpdate();
        closeConnection();
    }

    private void getAllCourse(ArrayList<Course> al, ResultSet rs) throws Exception {
        while (rs.next()) {
            Course c = new Course();
            c.setId(rs.getInt("id"));
            c.setName(rs.getString("name"));
            c.setTeacher(rs.getString("teacher"));
            c.setCollege(rs.getString("college"));
            c.setStu_id(rs.getString("stu_id"));
            al.add(c);

        }
    }

    public ArrayList<Course> getOnePage(int page, int size) throws Exception {
        ArrayList<Course> al = new ArrayList<>();
        initConnection();
        String sql = "SELECT * FROM course limit ?, ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, (page - 1) * size);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        getAllCourse(al, rs);
        closeConnection();
        return al;
    }

    public int getCount() throws Exception {
        initConnection();
        String sql = "select count(*) from course";
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        rs.next();
        int count = rs.getInt(1);
        closeConnection();
        return count;

    }

    public Course findWithId(String id) throws Exception {
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from course where id = '" + id + "'";
        ResultSet rs = stat.executeQuery(sql);
        Course c = getCourse(rs);
        closeConnection();
        return c;
    }

    private Course getCourse(ResultSet rs) throws Exception {
        Course c = null;
        if (rs.next()) {
            c = new Course();
            c.setId(rs.getInt("id"));
            c.setName(rs.getString("name"));
            c.setTeacher(rs.getString("teacher"));
            c.setCollege(rs.getString("college"));
            c.setStu_id(rs.getString("stu_id"));
        }
        return c;
    }

    public boolean insertCourse(String id, String name, String teacher, String college,String stu_id) throws Exception {
        initConnection();
        String sql = "insert into course(id,name, teacher, college,stu_id) values(?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ps.setString(2, name);
        ps.setString(3, teacher);
        ps.setString(4, college);
        ps.setString(5,stu_id);
        int i = ps.executeUpdate();
        closeConnection();
        return i == 1;
    }

    public List<Course> findWithStuId(String stu_id) throws Exception{
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from course where stu_id = '" + stu_id + "'";
        ResultSet rs = stat.executeQuery(sql);
        ArrayList<Course> list = new ArrayList<>();
        getAllCourse(list,rs);
        closeConnection();
        return list;
    }

    public Course getCourseById(String courseId) throws Exception{
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from course where id = '" + courseId + "'";
        ResultSet rs = stat.executeQuery(sql);
        Course c = getCourse(rs);
        closeConnection();
        return c;
    }


    public Set<Course> getCoursesByTeacherId(String id) throws Exception{
        initConnection();
        String sql = "select * from course where teacher_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ResultSet rs = ps.executeQuery();
        Set<Course> courses = new java.util.HashSet<>();
        while (rs.next()){
            Course c = new Course();
            c.setId(rs.getInt("id"));
            c.setName(rs.getString("name"));
            c.setTeacher(rs.getString("teacher"));
            c.setCollege(rs.getString("college"));
            c.setStu_id(rs.getString("stu_id"));
            courses.add(c);
        }
        closeConnection();
        return courses;
    }

    public List<String> getStuIdsByCourseAndTeacherId(String courseId, String teacher_id) throws Exception{
        initConnection();
        String sql = "select stu_id from course where id = ? and teacher_id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, courseId);
        ps.setString(2, teacher_id);
        ResultSet rs = ps.executeQuery();
        List<String> stu_ids = new ArrayList<>();
        while (rs.next()){
            stu_ids.add(rs.getString("stu_id"));
        }
        closeConnection();
        return stu_ids;

    }
}
